<?php

namespace App\Exports;

use PhpOffice\PhpSpreadsheet\Shared\Date;            // 日期处理
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;    // 数字格式化
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\StringValueBinder;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Concerns\FromQuery;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Events\BeforeSheet;
use Maatwebsite\Excel\Events\AfterSheet;

class ClaseSheet extends StringValueBinder implements FromQuery, WithTitle, WithHeadings,
                                            WithColumnFormatting, WithCustomValueBinder, WithEvents
{

    private $id, $name;

    public function __construct($clase)        // 导入外部查询参数
    {
        $this->id = $clase->id;

        $this->name = $clase->name;
    }

    // 查询
    public function query()
    {
        $result = \App\Models\Edu\Tuser::query()
            ->leftJoin('biz_users', 'biz_users.id', '=', 'edu_users.uid')
            /*->select('biz_users.account','biz_users.name',
                    'biz_users.mobile','biz_users.idcard','edu_users.created_at',
                    'edu_users.status')*/
            ->selectRaw('bank_biz_users.account, bank_biz_users.name, bank_biz_users.mobile,
                    bank_biz_users.idcard, bank_edu_users.created_at,
                    CASE WHEN bank_edu_users.status=1 THEN "报名成功" 
                        WHEN bank_edu_users.status=2 THEN "录取" 
                        ELSE "未录取" END')
            ->where('clase_id', $this->id);

        return $result;

    }


    //格式化
    public function registerEvents(): array
    {
        return [
            // Handle by a closure.
            /*BeforeExport::class => function(BeforeExport $event) {
                $event->writer->getProperties()->setCreator('Patrick');
            },

            // Array callable, refering to a static method.
            BeforeWriting::class => [self::class, 'beforeWriting'],*/

            // Using a class with an __invoke method.
            AfterSheet::class => [self::class, 'AfterSheetHandler']
        ];
    }


    public static function AfterSheetHandler(AfterSheet $event)
    {
        // 所有表头-设置字体为14
        $cellRange = 'A1:F1';
        //$event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);

        // 将第一行行高设置为20
        $event->sheet->getDelegate()->getDefaultRowDimension()->setRowHeight(20);

        //设置列宽
        $event->sheet->getDelegate()->getColumnDimension('B')->setWidth(11);
        $event->sheet->getDelegate()->getColumnDimension('C')->setWidth(15);
        $event->sheet->getDelegate()->getColumnDimension('D')->setWidth(25);
        $event->sheet->getDelegate()->getColumnDimension('E')->setWidth(22);

        //设置区域单元格垂直居中
        $event->sheet->getDelegate()->getParent()->getDefaultStyle()->getAlignment()->setVertical('center');
        $event->sheet->getDelegate()->getParent()->getDefaultStyle()
            ->applyFromArray([
                'font' => [
                    'name' => 'Microsoft Yahei',
                    'bold' => false,
                    'italic' => false,
                    'strikethrough' => false,
                    'color' => [
                        'rgb' => '000000'
                    ]
                ]

            ]);

    }


    // Excel 电子表单名
    public function title(): string
    {
        return $this->name;
    }


    public function columnFormats(): array
    {
        return [
            'C' => NumberFormat::FORMAT_GENERAL,
            'D' => NumberFormat::FORMAT_GENERAL,
        ];
    }

    public function headings(): array
    {
        return [
            '账号', '姓名', '手机', '证件号码', '报名日期', '状态'
        ];
    }
}
